"""cabinet"""
from datetime import datetime
from flask import g
from psycopg2 import extras
import pytz



def modify_updated_at(id_card):
    """更新时间"""
    with g.db_conn.cursor(cursor_factory=extras.RealDictCursor) as cursor:
        exec_query = f"""
            UPDATE 
                cabinet
                SET updated_at = {int(datetime.now(pytz.timezone('Asia/Shanghai')).timestamp())}
            FROM 
                persons
            WHERE 
                cabinet.id = persons.box_id
                AND persons.id_card = %s
            """
        cursor.execute(exec_query, (id_card,))
        g.db_conn.commit()


def info(id_card):
    """box"""
    with g.db_conn.cursor(cursor_factory=extras.RealDictCursor) as cursor:
        query = """
            SELECT 
                "position", position_id, "row", "column", ip_address
            FROM 
                cabinet c
            LEFT JOIN 
                persons p
                ON p.box_id = c.id AND p.deleted_at IS NULL 
            WHERE p.id_card = %s
        """
        cursor.execute(query, (id_card,))
        return cursor.fetchone()